﻿Imports System.IO
Imports MySql.Data.MySqlClient

''' <summary>
''' This console application is provided as is under GNU GPL V3 license.
''' The author of this is Alexander Schick
''' Use this application at your own risk. A general knowledge of SQL is nesessary.
''' The application ICON has been provided by http://www.doublejdesign.co.uk/ under CC Attribution 3.0 license
''' </summary>
''' <remarks></remarks>
Module Module1

    ''' <summary>
    ''' Contains the options set by the provided program arguments
    ''' </summary>
    ''' <remarks></remarks>
    Structure ProgramOptions
        Dim FieldTermination As String
        Dim EncloseSign As String
        Dim EscapeSign As String
        Dim LineTermination As String
        Dim QueryFilePath As FileInfo
        Dim ResultFilepath As FileInfo
        Dim ConnString As String
        Dim IncludeHeaders As Boolean
    End Structure

    Dim Options As ProgramOptions

    ''' <summary>
    ''' Doing the main work
    ''' </summary>
    ''' <remarks></remarks>
    Sub Main()
        'Set defaults
        Options.FieldTermination = ","

        'Show help and leave
        If CommandLine.ContainsArgument("help") Then
            ShowHelp()
            Exit Sub
        End If

        'Check if at least in and out file given
        If CommandLine.Args.Count > 2 Then
            If Not SetArguments() Then Exit Sub
        Else
            Console.WriteLine("Not enough parameters specified. At least Connectionstring, in and out file need to be provided")
            ShowHelp()
            Exit Sub
        End If

        Try
            Dim ResultOutRows As New Specialized.StringCollection
            Using stream = New StreamWriter(Options.ResultFilepath.FullName, False)
                Using con = New MySqlConnection(Options.ConnString)
                    con.Open()
                    Using QueryReader = New StreamReader(Options.QueryFilePath.FullName)
                        Using cmd = New MySqlCommand(QueryReader.ReadToEnd, con)
                            Using reader = cmd.ExecuteReader()
                                Dim HeadersWritten As Boolean = False
                                While reader.Read()
                                    Dim ResultOutRow As String = ""

                                    'Write headers once if requested
                                    If Options.IncludeHeaders And HeadersWritten = False Then
                                        'Last one?
                                        For FieldReader = 0 To reader.FieldCount - 1
                                            If FieldReader = reader.FieldCount - 1 Then
                                                ResultOutRow += PreparField(reader.GetName(FieldReader).ToString)
                                            Else
                                                ResultOutRow += PreparField(reader.GetName(FieldReader).ToString) & Options.FieldTermination
                                            End If
                                        Next
                                        ResultOutRows.Add(ResultOutRow)
                                        ResultOutRow = ""
                                        HeadersWritten = True
                                    End If

                                    For FieldReader = 0 To reader.FieldCount - 1
                                        'Last one?
                                        If FieldReader = reader.FieldCount - 1 Then
                                            ResultOutRow += PreparField(reader.GetValue(FieldReader).ToString)
                                        Else
                                            ResultOutRow += PreparField(reader.GetValue(FieldReader).ToString) & Options.FieldTermination
                                        End If
                                    Next
                                    ResultOutRows.Add(ResultOutRow)
                                    ResultOutRow = ""
                                End While
                            End Using
                        End Using
                    End Using
                End Using
                For i = 0 To ResultOutRows.Count - 1
                    If i = ResultOutRows.Count - 1 Then
                        stream.Write(ResultOutRows(i))
                    Else
                        stream.Write(ResultOutRows(i) & Options.LineTermination)
                    End If

                Next
            End Using

        Catch ex As Exception
            Console.WriteLine("Exception raised! Error message:")
            Console.Write(ex.Message)
            Exit Sub
        End Try
    End Sub

    Function PreparField(FieldValue) As String
        PreparField = FieldValue

        PreparField = PreparField.Replace(Options.EscapeSign, Options.EscapeSign & Options.EscapeSign)
        PreparField = PreparField.Replace(Options.LineTermination, Options.EscapeSign & Options.LineTermination)
        PreparField = PreparField.Replace(Options.FieldTermination, Options.EscapeSign & Options.FieldTermination)
        PreparField = PreparField.Replace(Options.EncloseSign, Options.EscapeSign & Options.EncloseSign)

        PreparField = Options.EncloseSign & PreparField & Options.EncloseSign
    End Function

    ''' <summary>
    ''' Will print the help text into the console
    ''' </summary>
    ''' <remarks></remarks>
    Sub ShowHelp()
        Dim HelpText As String
        HelpText = "Tool to store a MySQL query result into a local CSV file." & vbCrLf
        HelpText += "Use at your own risk. The Author will not be accountable for anny issues produced by this application. This application is provided under the GNU GPLv3 license. " & vbCrLf
        HelpText += "" & vbCrLf
        HelpText += "Usage: MySQL2CSV.exe [options] /Con:Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword; QueryFile OutCsvFile" & vbCrLf
        HelpText += "" & vbCrLf
        HelpText += "/Con: " & vbTab & "MYSQL connection string" & vbCrLf
        HelpText += "QueryFile " & vbTab & "Path to a file containing the select statement" & vbCrLf
        HelpText += "OutCsvFile " & vbTab & "Path to a file to store the reslut in CSV format" & vbCrLf
        HelpText += "Options: " & vbCrLf
        HelpText += "/FieldsTerminatedBy=[Termination sign] " & vbTab & "like , or ; default is , " & vbCrLf
        HelpText += "/EnclosedBy=[Enclosing sign] " & vbTab & "Use ASCI code like 34 for " & Chr(34) & " or 39 for ' " & vbCrLf
        HelpText += "/EscapedBy=[Escape sign] " & vbTab & "like \\" & vbCrLf
        HelpText += "/LinesTerminatedBy=[row termination control sign] " & vbTab & "possible values 'LF', 'CRLF', 'CR'" & vbCrLf
        HelpText += "/IncludeHeaders " & vbTab & "Will enclude field headers" & vbCrLf
        HelpText += "/Help " & vbTab & "Will show this help" & vbCrLf
        HelpText += "" & vbCrLf
        HelpText += "Example call: MySQL2CSV.exe  /FieldsTerminatedBy=; /EnclosedBy=34 /EscapedBy=\ /LinesTerminatedBy=CRLF /Con=Server=localhost;Database=myDataBase;Uid=myUsername;Pwd=myPassword; c:\temp\MyQuery.sql c:\temp\MyResult.csv" & vbCrLf
        HelpText += "" & vbCrLf

        Console.Write(HelpText)
    End Sub

    ''' <summary>
    ''' Set the options based on the arguments
    ''' </summary>
    ''' <returns>returns true if successfull</returns>
    ''' <remarks></remarks>
    Function SetArguments() As Boolean
        Try
            If CommandLine.ContainsArgument("/FieldsTerminatedBy") Then
                Options.FieldTermination = CommandLine.GetCommandlineArgumentValue("FieldsTerminatedBy")
            End If
            If CommandLine.ContainsArgument("/EnclosedBy") Then
                Options.EncloseSign = Chr(CommandLine.GetCommandlineArgumentValue("EnclosedBy"))
            End If
            If CommandLine.ContainsArgument("/EscapedBy") Then
                Options.EscapeSign = CommandLine.GetCommandlineArgumentValue("EscapedBy")
            End If
            If CommandLine.ContainsArgument("/LinesTerminatedBy") Then
                Select Case CommandLine.GetCommandlineArgumentValue("LinesTerminatedBy")
                    Case "LF"
                        Options.LineTermination = vbLf
                    Case "CR"
                        Options.LineTermination = vbCr
                    Case "CRLF"
                        Options.LineTermination = vbCrLf
                End Select
            End If
            If CommandLine.ContainsArgument("/IncludeHeaders") Then
                Options.IncludeHeaders = True
            End If
            If CommandLine.ContainsArgument("/Con") Then
                Options.ConnString = CommandLine.GetCommandlineArgumentValue("Con")
            End If

            'Check if second last arg is a valid file
            Dim QueryFile As New FileInfo(CommandLine.Args(CommandLine.Args.Count - 2))
            If QueryFile.Exists Then
                Options.QueryFilePath = QueryFile
            End If

            Dim ResultFile As New FileInfo(CommandLine.Args(CommandLine.Args.Count - 1))
            Options.ResultFilepath = ResultFile
            SetArguments = True
        Catch ex As Exception
            Console.WriteLine("Exception raised! Error message:")
            Console.Write(ex.Message)
            SetArguments = False
        End Try
    End Function
End Module
